package domain;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class sales {
    private String name;
    private int buynum;

    public int getBuynum() {
        return buynum;
    }

    public String getName() {
        return name;
    }

    public void setBuynum(int buynum) {
        this.buynum = buynum;
    }

    public void setName(String name) {
        this.name = name;
    }
    public static List<sales> search(String year, String month){

            List<sales> ps = new ArrayList<sales>();
            Connection conn = null;//将应用程序链接到特定的数据库
            Statement stmt = null;//在一个给定的连接中，用于执行一个静态的数据库SQL语句
            ResultSet rs = null;//sql语句执行完后，返回的数据结果集，最初指向结果集第一行的前方,rs里面有大量get方法，字段的索引是从1开始
            try {
                Class.forName("com.mysql.cj.jdbc.Driver");
                //2.建立连接
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bookstore?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=CST", "root", "111111");
                //3.预
                stmt = conn.createStatement();
                String sql = "SELECT products.name,SUM(orderitem.buynum) totalsalnum "
                        + "FROM orders,products,orderItem "
                        + "WHERE orders.id=orderItem.order_id "
                        + "AND products.id=orderItem.product_id AND orders.paystate=1 "
                        + "AND year(ordertime)= "+year+" and month(ordertime)="+month+" "
                        + "GROUP BY products.name ORDER BY totalsalnum DESC";
                //4.执行
                rs = stmt.executeQuery(sql);//向数据库发sql的请求,执行查询语句，查询出来结果集
                while (rs.next()) {//有多条数据循环遍历结果集
                    sales p = new sales();
                    p.setName(rs.getString("name"));
                    p.setBuynum(rs.getInt("buynum"));
                    ps.add(p);
                }
            }catch(Exception e){
                e.printStackTrace();
            }
            //5.释放
            finally{
                if (rs != null) {
                    try {
                        rs.close();
                    } catch (SQLException e) {
                        throw new RuntimeException();
                    }
                    rs = null;
                }
                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (SQLException e) {
                        throw new RuntimeException();
                    }
                    stmt = null;
                }
                if (conn != null) {
                    try {
                        conn.close();
                    } catch (SQLException e) {
                        throw new RuntimeException();
                    }
                    conn = null;
                }
            }
            return ps;
        }
}
